using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using RikMigrations.Providers;

namespace RikMigrations.Tests
{
	public abstract class DbProviderTests
	{
		protected const string SetupTestTableName = "SetupTestTable";
        protected const string SetupTestColumnName = "ID";
	    protected const string SetupTestViewName = "SetupTestView";

	    protected string SetupTestViewSql =
            string.Format(@"select top 100 percent {0} from {1}", "ID",SetupTestTableName);

		protected DbProvider db;
		public DbProviderTests(DbProvider db)
		{
			this.db = db;
		}

		protected abstract void CreateSetupTestTable();

		[SetUp]
		public virtual void Setup()
		{
			db.CreateTransaction();
			CreateSetupTestTable();
		}

		[TearDown]
		public virtual void TearDown()
		{
			db.RollbackTransaction();
		}

		[Test]
		public void TableExists()
		{
			Assert.IsTrue(db.TableExists(SetupTestTableName));
			Assert.IsFalse(db.TableExists(Guid.NewGuid().ToString()));
		}

		[Test]
		public void ColumnExists()
		{
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "ID"));
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, Guid.NewGuid().ToString()));
		}

		[Test]
		public void AddTable()
		{
			Assert.IsFalse(db.TableExists("Test"));
			Table table = db.AddTable("Test");
            table.AddColumn("ID", typeof(int));
			Assert.IsFalse(db.TableExists("Test"));
			table.Save();
			Assert.IsTrue(db.TableExists("Test"));
		}

        [Test]
        public void AddView()
        {
            Assert.IsFalse(db.ViewExists("vw_test"));
            View view = db.AddView("vw_test", SetupTestViewSql);
            Assert.IsTrue(view.Script.Equals(SetupTestViewSql));
            Assert.IsFalse(db.ViewExists("vw_test"));
            Assert.IsTrue(db.TableExists(SetupTestTableName));
            view.Save();
            Assert.IsTrue(db.ViewExists("vw_test"));
        }

		[Test]
		public void DropTable()
		{
			Assert.IsTrue(db.TableExists(SetupTestTableName));
			db.DropTable(SetupTestTableName);
			Assert.IsFalse(db.TableExists(SetupTestTableName));
		}

        [Test]
        public void DropView()
        {
            Assert.IsTrue(db.ViewExists(SetupTestViewName));
            db.DropView(SetupTestViewName);
            Assert.IsFalse(db.ViewExists(SetupTestViewName));
        }

		[Test]
		public void AddColumn()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int));
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			table.Save();
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
		}

        [Test]
        public void AddGenericColumn()
        {
            Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
            Table table = db.AlterTable(SetupTestTableName);
            table.AddColumn<int>("Test");
            Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
            table.Save();
            Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
        }

		[Test]
		public void AddPKConstraint()
		{
			Table table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddConstraint("PK_Test", ConstraintType.PrimaryKey, "ID");
			table.Save();
		}

		[Test]
		public void AddPKConstraint2()
		{
			Table table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddColumn("OtherID", typeof(int));
			table.AddConstraint("PK_Test", ConstraintType.PrimaryKey, "ID", "OtherID");
			table.Save();
		}

		[Test]
		public void AddUniqueConstraint()
		{
			Table table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddConstraint("PK_Test", ConstraintType.Unique, "ID");
			table.Save();
		}

		[Test]
		public void AddUniqueConstraint2()
		{
			Table table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddColumn("OtherID", typeof(int));
			table.AddConstraint("PK_Test", ConstraintType.Unique, "ID", "OtherID");
			table.Save();
		}

		[Test]
		public void AddForeignKey()
		{
			Table table = db.AddTable("Table1");
            table.AddColumn("ID", typeof(int)).PrimaryKey();
			table.Save();

			table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddColumn("OtherID", typeof(int));
			table.Save();

            table.AddForeignKey("FK_Test", "OtherID", "Table1", "ID");
			table.Save();
		}

		[Test]
		public void AddForeignKeyWithCascade()
		{
			Table table = db.AddTable("Table1");
            table.AddColumn("ID", typeof(int)).PrimaryKey();
			table.Save();

			table = db.AddTable("AnotherTable");
            table.AddColumn("ID", typeof(int));
			table.AddColumn("OtherID", typeof(int));
			table.Save();

            table.AddForeignKey("FK_Test", "OtherID", "Table1", "ID").OnDelete(CascadeOptions.Cascade).OnUpdate(CascadeOptions.Cascade);
			table.Save();
		}

        [Test]
        public void InsertingDataWithIdentity()
        {
            Table table = db.AddTable("Table1");
            table.AddColumn<int>("ID").PrimaryKey().AutoGenerate();
            table.AddColumn<string>("Name", 20);
            table.Insert(new { ID = 1, Name = "Hello World" });
            table.Save();            
            
            Assert.AreEqual(1, db.ExecuteScalar("select count(Id) from Table1"));
        }

        [Test]
        public void InsertingDataWithOutIdentity()
        {
            Table table = db.AddTable("Table1");
            table.AddColumn<int>("ID").PrimaryKey();
            table.AddColumn<string>("Name", 20);
            table.Insert(new { ID = 1, Name = "Hello World" });
            table.Save();

            Assert.AreEqual(1, db.ExecuteScalar("select count(Id) from Table1"));
        }

        [Test]
        public void InsertingDataWithIdentityFromProvider()
        {
            Table table = db.AddTable("Table1");
            table.AddColumn<int>("ID").PrimaryKey().AutoGenerate();
            table.AddColumn<string>("Name", 20);
            table.Save();

            db.InsertDataInto("Table1", new { ID = 1, Name = "Hello World" }, true);

            Assert.AreEqual(1, db.ExecuteScalar("select count(Id) from Table1"));
        }

        [Test]
        public void InsertingDataWithOutIdentityFromProvider()
        {
            Table table = db.AddTable("Table1");
            table.AddColumn<int>("ID").PrimaryKey();
            table.AddColumn<string>("Name", 20);
            table.Save();

            db.InsertDataInto("Table1", new { ID = 1, Name = "Hello World" }, false);

            Assert.AreEqual(1, db.ExecuteScalar("select count(Id) from Table1"));
        }


		#region Drop Columns
		[Test]
		public void DropColumn()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int));
			table.Save();
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
			table.DropColumn("Test");
			table.Save();
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
		}

		[Test]
		public void DropUniqueColumn()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int)).Unique();
			table.Save();
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
			table.DropColumn("Test");
			table.Save();
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
		}

		[Test]
		public void DropNotNullColumn()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int)).NotNull().Default(0);
			table.Save();
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
			table.DropColumn("Test");
			table.Save();
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
		}

		[Test]
		public void DropIdentityColumn()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int)).AutoGenerate();
			table.Save();
			Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
			table.DropColumn("Test");
			table.Save();
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
		}

		[Test]
		public void DropForeignKeyColumn()
		{
			try
			{
				Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
				Table table = db.AlterTable(SetupTestTableName);
				table.AddColumn("Test", typeof(int)).PrimaryKey();
				table.Save();

				Table table2 = db.AddTable("TestTable");
				table2.AddColumn("Test", typeof(int)).References(SetupTestTableName, "Test");
				table2.Save();

				Assert.IsTrue(db.ColumnExists(SetupTestTableName, "Test"));
				Assert.IsTrue(db.ColumnExists("TestTable", "Test"));
				table.DropColumn("Test");
				table.Save();
				Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			}
			catch (Exception)
			{
				// There's no specific exception expected at this time so can't use built-in nunit ExpectedException attribute
				return;
			}
			throw new Exception("Expected exception");
		}

		[Test]
		public void DropForeignKeyColumn2()
		{
			Assert.IsFalse(db.ColumnExists(SetupTestTableName, "Test"));
			Table table = db.AlterTable(SetupTestTableName);
			table.AddColumn("Test", typeof(int)).PrimaryKey();
			table.Save();

			Table table2 = db.AddTable("TestTable");
			table2.AddColumn("ID", typeof(int));
			table2.AddColumn("Test", typeof(int)).References(SetupTestTableName, "Test");
			table2.Save();

			Assert.IsTrue(db.ColumnExists("TestTable", "Test"));
			table2.DropColumn("Test");
			table2.Save();
			Assert.IsFalse(db.ColumnExists("TestTable", "Test"));
		}
		#endregion

		#region Renaming
		[Test]
		public void RenameColumn()
		{
			CheckColumnNotExists("Table1", "Joe");
			CheckColumnNotExists("Table1", "James");

			Table table = db.AddTable("Table1");
			table.AddColumn("Joe", typeof(int));
			table.Save();
			CheckColumnExists("Table1", "Joe");
			CheckColumnNotExists("Table1", "James");

			Column c = table.AlterColumn("Joe");
			c.Rename("James");
			c.Save();
			CheckColumnExists("Table1", "James");
			CheckColumnNotExists("Table1", "Joe");
		}

		private void CheckColumnExists(string table, string column)
		{
			db.ExecuteScalar(string.Format("SELECT COUNT([{0}]) FROM [{1}]", column, table));
		}

		private void CheckColumnNotExists(string table, string column)
		{
			try
			{
				db.ExecuteScalar(string.Format("SELECT COUNT([{0}]) FROM [{1}]", column, table));
				Assert.Fail("Column '{0}.{1}' still exists", table, column);
			}
			catch
			{
			}
		}
		#endregion
	}
}